//
//  QSDatabaseManager.swift
//  TestSwift
//
//  Created by Song on 2018/5/18.
//  Copyright © 2018年 Song. All rights reserved.
//

import UIKit
import SQLite

/// 筛选方式
enum QSFilterType {
    case equal      // 相等
    case greater    // 大于
    case less       // 小于
}

class QSDatabaseManager: NSObject {
    /// 创建数据库
    ///
    /// - Parameters:
    ///   - path: 数据库路径
    ///   - dbName: 数据库名，以.sqlite3为文件后缀
    class func qs_createDatabase(path: String? = nil, dbName: String = "db.sqlite3") -> Connection? {
        var dbPath = String.init()
        
        if path != nil {
            if path!.hasSuffix("/") && dbName.hasPrefix("/") {
                dbPath = path!
                dbPath.remove(at: dbPath.endIndex)
            } else if !path!.hasSuffix("/") && !dbName.hasPrefix("/") {
                dbPath = path!
                dbPath = dbPath + "/"
            }
            dbPath = dbPath + dbName
        } else {
            let paths = NSSearchPathForDirectoriesInDomains(FileManager.SearchPathDirectory.documentDirectory, FileManager.SearchPathDomainMask.userDomainMask, true)
            let path1 = paths.first
            
            if dbName.hasPrefix("/") {
                dbPath = path1! + dbName
            } else {
                dbPath = path1! + "/" + dbName
            }
        }
        
        return try? Connection.init(dbPath)
    }
    
    /// 创建表  tId是主键
    ///
    /// - Parameters:
    ///   - db: 数据库
    ///   - tableName: 表名
    ///   - columnNames: 字段名数组（暂时只能创建String的字段）
    class func qs_createTable(db: Connection, tableName: String, columnNames: Array<String>) -> Table? {
        let table = Table.init(tableName)
        
        do {
            try db.run(table.create(ifNotExists: true, block: { (t) in
                // 主键
                let tId = Expression<Int64>("tId")
                t.column(tId, primaryKey: true)
                
                for columnName in columnNames {
                    let column = Expression<String>(columnName)
                    t.column(column)
                }
            }))
            
            return table
        } catch {
            #if DEBUG
            debugPrint(error)
            #endif
        }
        
        return nil
    }
    
    /// 插入数据
    ///
    /// - Parameters:
    ///   - db: 数据库
    ///   - table: 表
    ///   - dataDict: 字段和值的字典
    class func qs_insertData(db: Connection, table: Table, dataDict: Dictionary<String, String>) -> Bool {
        do {
            var columns = Array<Setter>.init()
            for key in dataDict.keys {
                let value = (Expression<String>(key) <- dataDict[key]!)
                columns.append(value)
            }
            
            let insert = table.insert(columns)
            try db.run(insert)
            
            return true
        } catch {
            #if DEBUG
            debugPrint(error)
            #endif
            
            return false
        }
    }
    
    /// 删除数据
    ///
    /// - Parameters:
    ///   - db: 数据库
    ///   - table: 表
    ///   - tId: 要删除数据的主键序号
    class func qs_deleteData(db: Connection, table: Table, tId: Int64) -> Bool {
        let filter = table.filter(Expression<Int64>("tId") == tId)
        
        do {
            try db.run(filter.delete())
            return true
        } catch  {
            #if DEBUG
            debugPrint(error)
            #endif
            
            return false
        }
    }
    
    /// 修改数据
    ///
    /// - Parameters:
    ///   - db: 数据库
    ///   - table: 表
    ///   - tId: 要更新数据的主键序号
    ///   - updateDict: 要更新的键值对字典
    class func qs_updateData(db: Connection, table: Table, tId: Int64, updateDict: Dictionary<String, String>) -> Bool {
        let filter = table.filter(Expression<Int64>("tId") == tId)
        
        do {
            var columns = Array<Setter>.init()
            for key in updateDict.keys {
                let value = (Expression<String>(key) <- updateDict[key]!)
                columns.append(value)
            }
            
            try db.run(filter.update(columns))
            
            return true
        } catch  {
            #if DEBUG
            debugPrint(error)
            #endif
            
            return false
        }
    }
    
    /// 读取数据
    ///
    /// - Parameters:
    ///   - db: 数据库
    ///   - table: 表
    ///   - columnNames: 字段数组
    /// - Returns: 数据字典数组，读取失败返回nil
    class func qs_readAllData(db: Connection, table: Table, columnNames: Array<String>) -> Array<Dictionary<String, Any>>? {
        var resultArray = Array<Dictionary<String, Any>>.init()
        
        let dataArray = try? db.prepare(table)
        
        if let dArray = dataArray {            
            for data in dArray {
                var dict = Dictionary<String, Any>.init()
                dict["tId"] = data[Expression<Int64>("tId")]
                
                for column  in columnNames {
                    dict[column] = String(data[Expression<String>(column)])
                }
                
                resultArray.append(dict)
            }
            
            return resultArray
        } else {
            return nil
        }
    }
    
    /// 查找符合条件的数据
    ///
    /// - Parameters:
    ///   - db: 数据库
    ///   - table: 表
    ///   - filterArray: 筛选的条件数组   数组元素是一个元组（字段名，值，筛选方式默认相等）
    ///   - columnNames: 字段数组
    /// - Returns: 数据字典数组
    class func qs_filteData(db: Connection, table: Table, filterArray:Array<(String, String, QSFilterType)>, columnNames: Array<String>) -> Array<Dictionary<String, Any>>? {
        var resultArray = Array<Dictionary<String, Any>>.init()
        
        // 筛选条件
        var predicate: Expression<Bool> = Expression<Int64>("tId") > -1
        
        for filter in filterArray {
            switch filter.2 {
            case .equal:
                predicate = predicate && (Expression<String>(filter.0) == filter.1)
            case .greater:
                predicate = predicate && (Expression<String>(filter.0) > filter.1)
            case .less:
                predicate = predicate && (Expression<String>(filter.0) < filter.1)
            }
        }
        
        let dataArray = try? db.prepare(table.filter(predicate))
        
        if let dArray = dataArray {
            for data in dArray {
                var dict = Dictionary<String, Any>.init()
                dict["tId"] = data[Expression<Int64>("tId")]
                
                for column  in columnNames {
                    dict[column] = String(data[Expression<String>(column)])
                }
                
                resultArray.append(dict)
            }
            
            return resultArray
        } else {
            return nil
        }
    }
    
    /// 获取表中数据的数量
    ///
    /// - Parameters:
    ///   - db: 数据库
    ///   - table: 表
    /// - Returns: 数量
    class func qs_getCount(db: Connection, table: Table) -> Int {
        let dataArray = try? db.prepare(table)
        
        if let dataArr = dataArray {
            return Array(dataArr).count
        }
        
        return 0
    }
}
